package com.mysql.jdbc.cacheutils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.*;


/**
 * 

* @ClassName: SqlParse

* @Description: TODO(用于保存SQL解析的结果，以便生成JSON)

* @author Administrator

* @date 2017-12-20 上午9:40:27

*
 */
public class SqlParse {

	//原SQL语句
	private String originSql;
	
	//操作类型（insert,delete,update,select）
	private String op;
	
	//表名
	private String table;
	
	//相关的每列的字段名，insert、select才涉及这项
	private ArrayList<String> columns;
	
	//相关的每列的修改（类似column=value），update操作才涉及这项
	//List嵌套list，内层list保存每一个表达式的三个属性
	private ArrayList<ArrayList<String>> changes = new ArrayList<ArrayList<String>>();
	
	//条件，对于insert是values()中的值；对于delete,update,select是where后面的内容
	private ArrayList<ArrayList<String>> conditions = new ArrayList<ArrayList<String>>();


	//解析sql，解析成功返回true，失败返回false
	public boolean parse(String sql){
		//增：操作表、列名、列值
		//改：操作表、操作列、条件
		//删：操作表、条件
		
		//转小写处理，原sql保存到originSql中，解析操作类型
		originSql = sql;
		String lowerCaseSqlString = sql.toLowerCase();
		String pattern = "insert|delete|update|select";
		Pattern p = Pattern.compile(pattern);
		Matcher m = p.matcher(lowerCaseSqlString);
		if(m.find()){
			setOp(m.group(0));
		}else{
			return false;
		}
		
		//根据不同的操作类型分别解析
		//查询：查询字段、操作表、条件
		if(op.equals("select")){
			//获取查询字段
			pattern = "(?<=select)(.+)(?=from)";
			p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) { 
		    	String columnsString = m.group(0).replace(" ", "");
		    	columns = new ArrayList(Arrays.asList(columnsString.split(",")));
		    }else{
		    	return false;
		    }
		    
		    //获取表名。先判断有没有where，然后分情况截取
		    if(lowerCaseSqlString.indexOf("where", 0) != -1){
			    pattern = "(?<=from)(.+)(?=where)";
				p = Pattern.compile(pattern);
			    m = p.matcher(lowerCaseSqlString);
			    if (m.find( )) {
			    	table = m.group(0).replace(" ", "");
			    }else{
			    	return false;
			    }
		    }else{
			    pattern = "(?<=from)(.+)";
				p = Pattern.compile(pattern);
			    m = p.matcher(lowerCaseSqlString);
			    if (m.find( )) {
			    	table = m.group(0).replace(" ", "");
			    }else{
			    	return false;
			    }
		    }

		    
		    //获取条件
		    pattern = "(?<=where)(.+)";
		    p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) { 
		    	//先把 and 和 or 用*and*和*or*替换，然后去除'和空格，最后将*用空格替换
		    	String conditionsString = m.group(0).replace(" and ", "*and*").replace(" or ", "*or*").replace("'", "").replace(" ", "").replace("*", " ");
		    	ArrayList<String> tempConditions = new ArrayList(Arrays.asList(conditionsString.split(" ")));
		    	//将每项条件拆分为3项，逻辑运算符单独为一项，如name=pen and拆为[neme,pen,=],[and]
		    	for(int i=0;i<tempConditions.size();i++){
		    		String conditionString = tempConditions.get(i);
		    		//考虑到条件项中存在的and or情况
		    		if(conditionString.equals("and")){
		    			conditions.add(new ArrayList(Arrays.asList("and")));
		    		}else if(conditionString.equals("or")){
		    			conditions.add(new ArrayList(Arrays.asList("or")));
		    		}else{
		    			String op = "";
		    			if(conditionString.indexOf("=")!=-1){
		    				op = "=";
		    			}
		    			if(conditionString.indexOf("<")!=-1){
		    				op = "<";
		    			}
		    			if(conditionString.indexOf(">")!=-1){
		    				op = ">";
		    			}
		    			if(conditionString.indexOf("<=")!=-1){
		    				op = "<=";
		    			}
		    			if(conditionString.indexOf(">=")!=-1){
		    				op = ">=";
		    			}
		    			ArrayList<String> AtempCondition = new ArrayList(Arrays.asList(conditionString.split(op)));
		    			AtempCondition.add(op);
		    			conditions.add(AtempCondition);
		    		} 		
		    	}
		    }else{
		    	return false;
		    }
		}else if(op.equals("delete")){
			//删操作：表名，条件
			
			//获取表名
			pattern = "(?<=from)(.+)(?=where)";
			p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) {
		    	table = m.group(0).replace(" ", "");
		    }else{
		    	return false;
		    }
		    
		    //获取条件
		    pattern = "(?<=where)(.+)";
		    p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) {
		    	String conditionsString = m.group(0).replace(" and ", "*and*").replace(" or ", "*or*").replace("'", "").replace(" ", "").replace("*", " ");
		    	ArrayList<String> tempConditions = new ArrayList(Arrays.asList(conditionsString.split(" ")));
		    	//将每项条件拆分为3项，逻辑运算符单独为一项，如name=pen and拆为[neme,pen,=],[and]
		    	for(int i=0;i<tempConditions.size();i++){
		    		String conditionString = tempConditions.get(i);
		    		//考虑到条件项中存在的and or情况
		    		if(conditionsString.equals("and")){
		    			conditions.add(new ArrayList(Arrays.asList("and")));
		    		}else if(conditionsString.equals("or")){
		    			conditions.add(new ArrayList(Arrays.asList("or")));
		    		}else{
		    			String op = "";
		    			if(conditionString.indexOf("=")!=-1){
		    				op = "=";
		    			}
		    			if(conditionString.indexOf("<")!=-1){
		    				op = "<";
		    			}
		    			if(conditionString.indexOf(">")!=-1){
		    				op = ">";
		    			}
		    			if(conditionString.indexOf("<=")!=-1){
		    				op = "<=";
		    			}
		    			if(conditionString.indexOf(">=")!=-1){
		    				op = ">=";
		    			}
		    			ArrayList<String> AtempCondition = new ArrayList(Arrays.asList(conditionString.split(op)));
		    			AtempCondition.add(op);
		    			conditions.add(AtempCondition);
		    		} 		
		    	}
		    }else{
		    	return false;
		    }
		}else if(op.equals("update")){
			//改操作：表名、列变动、条件
			
			//获取表名
			pattern = "(?<=update)(.+)(?=set)";
			p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) {
		    	table = m.group(0).replace(" ", "");
		    }else{
		    	return false;
		    }
			
			//获取每列的修改
			pattern = "(?<=set)(.+)(?=where)";
			p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) {
		    	String changesString = m.group(0).replace("'", "").replace(" ", "");
		    	List<String> tempChanges = Arrays.asList(changesString.split(","));
		    	//将每列的更新拆分为3项，如price=7拆为[price,7,=]
		    	for(int i=0;i<tempChanges.size();i++){
		    		ArrayList<String> AtempChange = new ArrayList(Arrays.asList(tempChanges.get(i).split("=")));
			    	AtempChange.add("=");
			    	changes.add(AtempChange);
		    	}
		    }else{
		    	return false;
		    }
		    
		    //获取条件
		    pattern = "(?<=where)(.+)";
		    p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) { 
		    	String conditionsString = m.group(0).replace(" and ", "*and*").replace(" or ", "*or*").replace("'", "").replace(" ", "").replace("*", " ");
		    	ArrayList<String> tempConditions = new ArrayList(Arrays.asList(conditionsString.split(" ")));
		    	//将每项条件拆分为3项，逻辑运算符单独为一项，如name=pen and拆为[neme,pen,=],[and]
		    	for(int i=0;i<tempConditions.size();i++){
		    		String conditionString = tempConditions.get(i);
		    		//考虑到条件项中存在的and or情况
		    		if(conditionsString.equals("and")){
		    			conditions.add(new ArrayList(Arrays.asList("and")));
		    		}else if(conditionsString.equals("or")){
		    			conditions.add(new ArrayList(Arrays.asList("or")));
		    		}else{
		    			String op = "";
		    			if(conditionString.indexOf("=")!=-1){
		    				op = "=";
		    			}
		    			if(conditionString.indexOf("<")!=-1){
		    				op = "<";
		    			}
		    			if(conditionString.indexOf(">")!=-1){
		    				op = ">";
		    			}
		    			if(conditionString.indexOf("<=")!=-1){
		    				op = "<=";
		    			}
		    			if(conditionString.indexOf(">=")!=-1){
		    				op = ">=";
		    			}
		    			ArrayList<String> AtempCondition = new ArrayList(Arrays.asList(conditionString.split(op)));
		    			AtempCondition.add(op);
		    			conditions.add(AtempCondition);
		    		} 		
		    	}
		    }else{
		    	return false;
		    }
		}else if(op.equals("insert")){
			//增操作，表名、列名、列值
			
			//获取表名
			pattern = "(?<=into)(.+)(?=\\()";
			p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) {
		    	table = Arrays.asList(m.group(0).replace(" ", "").split("\\(")).get(0);
		    }else{
		    	return false;
		    }
		    
		    //获取列名
		    pattern = "(?<=\\()(.+)(?=value)";
			p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) {
		    	String changesString = m.group(0).replace(" ", "").replace(")", "");
		    	columns = new ArrayList(Arrays.asList(changesString.split(",")));
		    }else{
		    	return false;
		    }
		    
		    //获取列值
		    pattern = "(?<=value)(.+)";
			p = Pattern.compile(pattern);
		    m = p.matcher(lowerCaseSqlString);
		    if (m.find( )) {
		    	String changesString = m.group(0).replace(" ", "").replace("'", "").replace("(", "").replace(")", "");
		    	conditions = new ArrayList(Arrays.asList(changesString.split(",")));
		    }else{
		    	return false;
		    }
		}
		
		
		return true;
		
	}


	public String getOriginSql() {
		return originSql;
	}


	public void setOriginSql(String originSql) {
		this.originSql = originSql;
	}


	public String getOp() {
		return op;
	}


	public void setOp(String op) {
		this.op = op;
	}


	public String getTable() {
		return table;
	}


	public void setTable(String table) {
		this.table = table;
	}


	public ArrayList<String> getColumns() {
		return columns;
	}


	public void setColumns(ArrayList<String> columns) {
		this.columns = columns;
	}


	public ArrayList<ArrayList<String>> getChanges() {
		return changes;
	}


	public void setChanges(ArrayList<ArrayList<String>> changes) {
		this.changes = changes;
	}


	public ArrayList<ArrayList<String>> getConditions() {
		return conditions;
	}


	public void setConditions(ArrayList<ArrayList<String>> conditions) {
		this.conditions = conditions;
	}
	
	

}
